﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace TestAgentDatabases
{
    class SQLServerExpressTest : DatabaseTest
    {
        public override void DeleteDatabases()
        {
            int databases = GetDatabaseCount();

            using (var connection = CreateDatabaseConnection(0))
            {
                connection.Open();
                for (int i = 0; i < databases; i++)
                {
                    try
                    {
                        if (!File.Exists(Path.Combine(DatabaseDirectory, "db"+i+".mdf"))) continue;
                        Execute(connection, "use master; drop database db" + i + ";");
                    }
                    catch
                    {
                    }
                }
            }
        }

        protected override void InsertOrder(int databaseId, int orderIndex, IDbConnection connection, ref IDbCommand dbCommand)
        {
            if (dbCommand == null)
            {
                dbCommand = connection.CreateCommand();
                dbCommand.CommandText = string.Format("insert into [{0}].[dbo].[{1}] (amount, marketId, executionDate, status) values (@amount, @marketId, @executionDate, @status)", "db" + databaseId, TableName);
                AddParameter(dbCommand, "@amount", DbType.Currency);
                AddParameter(dbCommand, "@marketId", DbType.Int32);
                AddParameter(dbCommand, "@executionDate", DbType.DateTime);
                AddParameter(dbCommand, "@status", DbType.Int32);
            }

            ((IDbDataParameter)dbCommand.Parameters["@amount"]).Value = orderIndex;
            ((IDbDataParameter)dbCommand.Parameters["@marketId"]).Value = 1;
            ((IDbDataParameter)dbCommand.Parameters["@executionDate"]).Value = DateTime.UtcNow;
            ((IDbDataParameter)dbCommand.Parameters["@status"]).Value = 1;
            dbCommand.ExecuteNonQuery();
        }

        protected override string GetCreateTableSQL(int databaseId)
        {
            return string.Format("create table [{0}].[dbo].[{1}] (orderId integer identity primary key, marketId int not null, amount money not null, executionDate datetime not null, Status integer);",
                "db" + databaseId, TableName);
        }

        protected override void CreateDatabase(IDbConnection connection, int databaseId)
        {
            var dbName = "db" + databaseId;
            var createDbSql = string.Format("use master; Create database {0} on (name = {0}_dat, filename = '{1}')", dbName, Path.Combine(DatabaseDirectory, dbName + ".mdf"));
            Execute(connection, createDbSql);

            var useSql = string.Format("use {0}", dbName);
            Execute(connection, useSql);
        }

        private void Execute(IDbConnection connection, string sql)
        {
            using (IDbCommand dbCommand = connection.CreateCommand())
            {
                dbCommand.CommandTimeout = 60;
                dbCommand.CommandText = sql;
                dbCommand.ExecuteNonQuery();
            }
        }

        protected override IDbConnection CreateDatabaseConnection(int databaseId)
        {
            return GetDatabaseConnection(databaseId);
        }

        protected override IDbConnection GetDatabaseConnection(int databaseId)
        {
            return new SqlConnection(GetConnectionString(databaseId));
        }

        private string GetConnectionString(int databaseId)
        {
            return @"Data Source=.\SQLEXPRESS;Integrated Security=True;Connect Timeout=30;User Instance=True";
        }
    }
}